import pymysql
# 创建一个数据库连接对象
# db = pymysql.connect("localhost","root","","woniuATM",charset="utf8")
# 创建游标对象
# cur = db.cursor()
# 创建数据库
# sql1 = "create database woniuATM"
# 创建表格，一定要先连接数据库
#  sql2 = """
#    create table user_info(
#    username varchar(20) primary key,
#    password varchar(30),
#    phone char(11),
#    balance int
#    )engine = Innodb default charset = "utf8"
# """
# 新增数据
# sql3 = "insert into user_info values('admin','1234','13498695846',2300),('eight','2234','18898325446',1500)"
# 查询数据(注意：查询结果需要重新获取)
# sql4 = "select * from user_info"
# 删除表格
# sql5 = "drop table user_info"
# 修改数据
# sql6 = "update user_info set password = '0909' where username = 'eight'"
# 新增列
# sql7 = "alter table user_info add addr varchar(50)"
# 执行sql语句
# cur.execute(sql)
# 提交事务(手动)
# db.commit()
# 获取一条数据
# result = cur.fetchone()
# 获取所有数据(二维元组)
# result = cur.fetchall()
# 获取指定数量的元组，括号里面是查询记录的条数
# result = cur.fetchmany(2)
# cur.close()
# db.close()
# 输出需要查询的结果
# print(result)

# 创建数据库连接对象
def create_obj():
    db = pymysql.connect("localhost","root","","woniuATM",charset="utf8")
    cur = db.cursor()
    return db,cur

# 对数据库的增、删、改
def db_operation(sql):
    db,cur = create_obj()
    cur.execute(sql)
    db.commit()
    cur.close()
    db.close()

# db_operation("insert into user_info values('sunny','3234','15949749584',2300)")

# 对数据库的查询(一条数据）
def query_one(sql):
    db,cur = create_obj()
    cur.execute(sql)
    db.commit()
    content = cur.fetchone()
    cur.close()
    db.close()
    return content

# query_one("select * from user_info")

# 创建表格用于存放操作流水
def create_dbh():
    dbh = pymysql.connect("localhost","root","","woniuATM",charset="utf8")
    cur1 = dbh.cursor()
    sql = '''create table history1(
          id int primary key,
          username varchar(20),
          content varchar(100),
          foreign key (username) references user_info(username)
    )engine = Innodb default charset = "utf8"
    '''
    cur1.execute(sql)
    dbh.commit()
    cur1.close()
    dbh.close()

# 对数据库的查询(所有数据）
def query_all(sql):
    db,cur = create_obj()
    cur.execute(sql)
    db.commit()
    content = cur.fetchall()
    cur.close()
    db.close()
    return content

